package batch.pmgm;

import java.sql.*;
import java.util.*;
import java.util.Date;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.log4j.*;

import batch.common.db.*;
import batch.common.report.*;

public class PmgmSt_verify {
    private final String TODAY  = DateFormatUtils.format(new Date(), "yyyyMMdd");
    
    private final String MAILSUBJECT = "PmgmSt_verify Error! (" + TODAY + ")"; 

    private String batch_date;
    private boolean bVerificationResult = true;

    /*******************************************************************************************************/
    /* Name : PmgmSt_verify
    /* Arguments : none
    /* Return : none
    /* Description : constructor
    /*******************************************************************************************************/
    public PmgmSt_verify(){
        batch_date = TODAY;
    }

    /*******************************************************************************************************/
    /* Name : PmgmSt_verify
    /* Arguments : String(--yyyymmdd--)
    /* Return : none
    /* Description : constructor
    /*******************************************************************************************************/
    public PmgmSt_verify(String date){
        batch_date = date;
    }
    
    /*******************************************************************************************************/
    /* Name : main
    /* Arguments : String(--yyyymmdd--)
    /*******************************************************************************************************/
    public static void main(String[] args) {
        PmgmSt_verify pmgm_verify = null;

        if ( args==null || args.length==0 ) {
            pmgm_verify = new PmgmSt_verify();
        } else {
            pmgm_verify = new PmgmSt_verify(args[0]);
        }

        Logger.getLogger("pmgm.pmgmst_verify").info(" ( * ) Start.");
        pmgm_verify.start();
        Logger.getLogger("pmgm.pmgmst_verify").info(" ( * ) End.");
    }

    /*******************************************************************************************************/
    /* Name : start
    /* Arguments : none
    /* Return : none
    /* Description : execute each statistics function
    /*******************************************************************************************************/
    public void start()
    {
        Connection conn = null;

        try {
            // get connection
            conn = ConnectionManager.getConnection();
            conn.setAutoCommit(false);

            // get promotion list to be verified.
            Logger.getLogger("pmgm.pmgmst_verify").info(" ( * ) select promotion list to be verified.\n");
            Vector vPromo_list = selectPromotion(conn);
            Logger.getLogger("pmgm.pmgmst_verify").info(" ( * ) success. " + vPromo_list.size() + " selected. \n\n");

            // verify if each member stats are same as total table for selected promotions.
            if (vPromo_list.size() != 0) {
                Logger.getLogger("pmgm.pmgmst_verify").info(" ( * ) MEMBER(MEDIA) stats verification starts.\n");
                verify_mb(vPromo_list, conn);
                Logger.getLogger("pmgm.pmgmst_verify").info(" ( * ) MEMBER(MEDIA) ends.\n\n");

                Logger.getLogger("pmgm.pmgmst_verify").info(" ( * ) BRAND stats verification starts.\n");
                verify_brand(vPromo_list, conn);
                Logger.getLogger("pmgm.pmgmst_verify").info(" ( * ) BRAND ends.\n\n");

                Logger.getLogger("pmgm.pmgmst_verify").info(" ( * ) GRADE stats verification starts.\n");
                verify_grade(vPromo_list, conn);
                Logger.getLogger("pmgm.pmgmst_verify").info(" ( * ) GRADE ends.\n\n");

                Logger.getLogger("pmgm.pmgmst_verify").info(" ( * ) OPT stats verification starts.\n");
                verify_opt(vPromo_list, conn);
                Logger.getLogger("pmgm.pmgmst_verify").info(" ( * ) OPT ends.\n\n");

                Logger.getLogger("pmgm.pmgmst_verify").info(" ( * ) AGE stats verification starts.\n");
                verify_age(vPromo_list, conn);
                Logger.getLogger("pmgm.pmgmst_verify").info(" ( * ) AGE ends.\n\n");

                Logger.getLogger("pmgm.pmgmst_verify").info(" ( * ) GENDER stats verification starts.\n");
                verify_gender(vPromo_list, conn);
                Logger.getLogger("pmgm.pmgmst_verify").info(" ( * ) GENDER ends.\n\n");
            }

            // final commit
            conn.commit();
        } catch(Exception e) {
            if (conn != null) try { conn.rollback(); } catch (Exception ex) { ex.printStackTrace(); }
            
            e.printStackTrace();

            Logger.getLogger("pmgm.pmgmst_verify").info(" ( ! ) " + e.toString());

            StackTraceElement[] stElement = e.getStackTrace();
            for(int p=0; p<stElement.length;p++){
                Logger.getLogger("pmgm.pmgmst_verify").info(" ( ! ) " + stElement[p].toString());
            }

            ReportError.byEmail(MAILSUBJECT, e.toString());
        } finally {
            if (conn != null) try { conn.close(); } catch (Exception e) { e.printStackTrace(); }
        }

        if (!bVerificationResult) {
            ReportError.byEmail("PMT verification fail", "PMT verification fail");
        }
    }

    /*******************************************************************************************************/
    /* Name : selectPromotion
    /* Arguments : Connection
    /* Return : Vector vPromo_list (--table name array--)
    /* Description : select promotion list to be verified.
    /*******************************************************************************************************/
    public Vector selectPromotion(Connection conn) throws Exception {
        Vector vPromo_list = new Vector();
        StringBuffer sql = new StringBuffer();
        sql.append( "SELECT B.MST_CD");
        sql.append(" FROM G_MB_MEMBER A, G_PMGM_MST_SUB B");
        sql.append(" WHERE A.MEMBERGBN=B.MST_CD || B.SUB_CD");
        sql.append(      " AND A.CREATED >= TO_DATE('" + batch_date + "','YYYYMMDD')");
        sql.append(      " AND A.CREATED < TO_DATE('" + batch_date + "','YYYYMMDD')+1");
        sql.append(      " AND A.REG_COUNTRY = B.SITE_CD");
        sql.append(" GROUP BY B.MST_CD");

        Statement stmt = null;
        ResultSet rs = null;

        try{
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql.toString());
            while(rs.next()){
                String[] arrList = new String[1];
                arrList[0] = rs.getString(1);
                vPromo_list.addElement(arrList);
            }
        }
        finally {
            if (rs != null) try { rs.close(); } catch (Exception e) {}
            if (stmt != null) try { stmt.close(); } catch (Exception e) {}
        }

        return vPromo_list;
    }

    /*******************************************************************************************************/
    /* Name : verify_mb
    /* Arguments : Vector vPromo_list, Connection
    /* Return : void
    /* Description : verify if member statistics(G_PMGM_ST_MB) is same as total table's
    /*******************************************************************************************************/
    public void verify_mb(Vector vPromo_list, Connection conn) throws Exception {
        String sQuery1 = "SELECT SITE_CD, SUB_CD, SUM(CNT_NUM) FROM G_PMGM_ST_TOT WHERE MST_CD=? GROUP BY SITE_CD, SUB_CD";
        String sQuery2 = "SELECT SITE_CD, SUB_CD, SUM(CNT_NUM) FROM G_PMGM_ST_MB WHERE MST_CD=? GROUP BY SITE_CD, SUB_CD";
        PreparedStatement pstmt1 = null;
        PreparedStatement pstmt2 = null;

        try {
            pstmt1 = conn.prepareStatement(sQuery1);
            pstmt2 = conn.prepareStatement(sQuery2);

            for (int i=0 ; i<vPromo_list.size() ; i++) {
                String[] arrList = (String[])vPromo_list.elementAt(i);
                pstmt1.setString(1, arrList[0]);
                pstmt2.setString(1, arrList[0]);
                ResultSet rs1 = null;
                ResultSet rs2 = null;
                try {
                    rs1 = pstmt1.executeQuery();
                    rs2 = pstmt2.executeQuery();
                    if (!isSame(rs1, rs2)) {
                        Logger.getLogger("pmgm.pmgmst_verify").info(" ( ! ) promotion code[" + arrList[0] + "] does not match.\n");
                        bVerificationResult = false;
                    }
                }
                finally {
                    if (rs1!=null) try { rs1.close(); } catch (Exception e) {}
                    if (rs2!=null) try { rs2.close(); } catch (Exception e) {}
                }
            }
        }
        finally {
            if (pstmt1!=null) try { pstmt1.close(); } catch (Exception e) {}
            if (pstmt2!=null) try { pstmt2.close(); } catch (Exception e) {}
        }
    }

    public void verify_brand(Vector vPromo_list, Connection conn) throws Exception {
        String sQuery1 = "SELECT SITE_CD, HP_BRAND, SUM(CNT_NUM) FROM G_PMGM_ST_TOT WHERE MST_CD=? GROUP BY SITE_CD, HP_BRAND";
        String sQuery2 = "SELECT SITE_CD, HP_BRAND, SUM(CNT_NUM) FROM G_PMGM_ST_BRAND WHERE MST_CD=? GROUP BY SITE_CD, HP_BRAND";
        PreparedStatement pstmt1 = null;
        PreparedStatement pstmt2 = null;

        try {
            pstmt1 = conn.prepareStatement(sQuery1);
            pstmt2 = conn.prepareStatement(sQuery2);

            for (int i=0 ; i<vPromo_list.size() ; i++) {
                String[] arrList = (String[])vPromo_list.elementAt(i);
                pstmt1.setString(1, arrList[0]);
                pstmt2.setString(1, arrList[0]);
                ResultSet rs1 = null;
                ResultSet rs2 = null;
                try {
                    rs1 = pstmt1.executeQuery();
                    rs2 = pstmt2.executeQuery();
                    if (!isSame(rs1, rs2)) {
                        Logger.getLogger("pmgm.pmgmst_verify").info(" ( ! ) promotion code[" + arrList[0] + "] does not match.\n");
                        bVerificationResult = false;
                    }
                }
                finally {
                    if (rs1!=null) try { rs1.close(); } catch (Exception e) {}
                    if (rs2!=null) try { rs2.close(); } catch (Exception e) {}
                }
            }
        }
        finally {
            if (pstmt1!=null) try { pstmt1.close(); } catch (Exception e) {}
            if (pstmt2!=null) try { pstmt2.close(); } catch (Exception e) {}
        }
    }

    public void verify_grade(Vector vPromo_list, Connection conn) throws Exception {
        String sQuery1 = "SELECT SITE_CD, GRADE_CD, SUM(CNT_NUM) FROM G_PMGM_ST_TOT WHERE MST_CD=? GROUP BY SITE_CD, GRADE_CD";
        String sQuery2 = "SELECT SITE_CD, GRADE_CD, SUM(CNT_NUM) FROM G_PMGM_ST_GRADE WHERE MST_CD=? GROUP BY SITE_CD, GRADE_CD";
        PreparedStatement pstmt1 = null;
        PreparedStatement pstmt2 = null;

        try {
            pstmt1 = conn.prepareStatement(sQuery1);
            pstmt2 = conn.prepareStatement(sQuery2);

            for (int i=0 ; i<vPromo_list.size() ; i++) {
                String[] arrList = (String[])vPromo_list.elementAt(i);
                pstmt1.setString(1, arrList[0]);
                pstmt2.setString(1, arrList[0]);
                ResultSet rs1 = null;
                ResultSet rs2 = null;
                try {
                    rs1 = pstmt1.executeQuery();
                    rs2 = pstmt2.executeQuery();
                    if (!isSame(rs1, rs2)) {
                        Logger.getLogger("pmgm.pmgmst_verify").info(" ( ! ) promotion code[" + arrList[0] + "] does not match.\n");
                        bVerificationResult = false;
                    }
                }
                finally {
                    if (rs1!=null) try { rs1.close(); } catch (Exception e) {}
                    if (rs2!=null) try { rs2.close(); } catch (Exception e) {}
                }
            }
        }
        finally {
            if (pstmt1!=null) try { pstmt1.close(); } catch (Exception e) {}
            if (pstmt2!=null) try { pstmt2.close(); } catch (Exception e) {}
        }
    }

    public void verify_opt(Vector vPromo_list, Connection conn) throws Exception {
        String sQuery1 = "SELECT SITE_CD, OPT_CD, SUM(CNT_NUM) FROM G_PMGM_ST_TOT WHERE MST_CD=? GROUP BY SITE_CD, OPT_CD";
        String sQuery2 = "SELECT SITE_CD, OPT_CD, SUM(CNT_NUM) FROM G_PMGM_ST_OPT WHERE MST_CD=? GROUP BY SITE_CD, OPT_CD";
        PreparedStatement pstmt1 = null;
        PreparedStatement pstmt2 = null;

        try {
            pstmt1 = conn.prepareStatement(sQuery1);
            pstmt2 = conn.prepareStatement(sQuery2);

            for (int i=0 ; i<vPromo_list.size() ; i++) {
                String[] arrList = (String[])vPromo_list.elementAt(i);
                pstmt1.setString(1, arrList[0]);
                pstmt2.setString(1, arrList[0]);
                ResultSet rs1 = null;
                ResultSet rs2 = null;
                try {
                    rs1 = pstmt1.executeQuery();
                    rs2 = pstmt2.executeQuery();
                    if (!isSame(rs1, rs2)) {
                        Logger.getLogger("pmgm.pmgmst_verify").info(" ( ! ) promotion code[" + arrList[0] + "] does not match.\n");
                        bVerificationResult = false;
                    }
                }
                finally {
                    if (rs1!=null) try { rs1.close(); } catch (Exception e) {}
                    if (rs2!=null) try { rs2.close(); } catch (Exception e) {}
                }
            }
        }
        finally {
            if (pstmt1!=null) try { pstmt1.close(); } catch (Exception e) {}
            if (pstmt2!=null) try { pstmt2.close(); } catch (Exception e) {}
        }
    }

    public void verify_age(Vector vPromo_list, Connection conn) throws Exception {
        String sQuery1 = "SELECT SITE_CD, AGE_CD, SUM(CNT_NUM) FROM G_PMGM_ST_TOT WHERE MST_CD=? GROUP BY SITE_CD, AGE_CD";
        String sQuery2 = "SELECT SITE_CD, AGE_CD, SUM(CNT_NUM) FROM G_PMGM_ST_AGE WHERE MST_CD=? GROUP BY SITE_CD, AGE_CD";
        PreparedStatement pstmt1 = null;
        PreparedStatement pstmt2 = null;

        try {
            pstmt1 = conn.prepareStatement(sQuery1);
            pstmt2 = conn.prepareStatement(sQuery2);

            for (int i=0 ; i<vPromo_list.size() ; i++) {
                String[] arrList = (String[])vPromo_list.elementAt(i);
                pstmt1.setString(1, arrList[0]);
                pstmt2.setString(1, arrList[0]);
                ResultSet rs1 = null;
                ResultSet rs2 = null;
                try {
                    rs1 = pstmt1.executeQuery();
                    rs2 = pstmt2.executeQuery();
                    if (!isSame(rs1, rs2)) {
                        Logger.getLogger("pmgm.pmgmst_verify").info(" ( ! ) promotion code[" + arrList[0] + "] does not match.\n");
                        bVerificationResult = false;
                    }
                }
                finally {
                    if (rs1!=null) try { rs1.close(); } catch (Exception e) {}
                    if (rs2!=null) try { rs2.close(); } catch (Exception e) {}
                }
            }
        }
        finally {
            if (pstmt1!=null) try { pstmt1.close(); } catch (Exception e) {}
            if (pstmt2!=null) try { pstmt2.close(); } catch (Exception e) {}
        }
    }

    public void verify_gender(Vector vPromo_list, Connection conn) throws Exception {
        String sQuery1 = "SELECT SITE_CD, GENDER_CD, SUM(CNT_NUM) FROM G_PMGM_ST_TOT WHERE MST_CD=? GROUP BY SITE_CD, GENDER_CD";
        String sQuery2 = "SELECT SITE_CD, GENDER_CD, SUM(CNT_NUM) FROM G_PMGM_ST_GENDER WHERE MST_CD=? GROUP BY SITE_CD, GENDER_CD";
        PreparedStatement pstmt1 = null;
        PreparedStatement pstmt2 = null;

        try {
            pstmt1 = conn.prepareStatement(sQuery1);
            pstmt2 = conn.prepareStatement(sQuery2);

            for (int i=0 ; i<vPromo_list.size() ; i++) {
                String[] arrList = (String[])vPromo_list.elementAt(i);
                pstmt1.setString(1, arrList[0]);
                pstmt2.setString(1, arrList[0]);
                ResultSet rs1 = null;
                ResultSet rs2 = null;
                try {
                    rs1 = pstmt1.executeQuery();
                    rs2 = pstmt2.executeQuery();
                    if (!isSame(rs1, rs2)) {
                        Logger.getLogger("pmgm.pmgmst_verify").info(" ( ! ) promotion code[" + arrList[0] + "] does not match.\n");
                        bVerificationResult = false;
                    }
                }
                finally {
                    if (rs1!=null) try { rs1.close(); } catch (Exception e) {}
                    if (rs2!=null) try { rs2.close(); } catch (Exception e) {}
                }
            }
        }
        finally {
            if (pstmt1!=null) try { pstmt1.close(); } catch (Exception e) {}
            if (pstmt2!=null) try { pstmt2.close(); } catch (Exception e) {}
        }
    }

    /*******************************************************************************************************/
    /* Name : isSame
    /* Arguments : ResultSet rs1, ResultSet rs2
    /* Return : boolean
    /* Description : compare two ResultSets if they are same.
    /*******************************************************************************************************/
    public boolean isSame(ResultSet rs1, ResultSet rs2) throws Exception {
        int nColumnCount1 = rs1.getMetaData().getColumnCount();
        int nColumnCount2 = rs2.getMetaData().getColumnCount();

        if (nColumnCount1 != nColumnCount2) return false;

        Vector vector1 = new Vector();
        Vector vector2 = new Vector();

        while (rs1.next()) {
            String[] arrList = new String[nColumnCount1];
            for (int i=0 ; i<nColumnCount1 ; i++) {
                arrList[i] = rs1.getString(i+1);
            }
            vector1.addElement(arrList);
        }

        while (rs2.next()) {
            String[] arrList = new String[nColumnCount2];
            for (int i=0 ; i<nColumnCount2 ; i++) {
                arrList[i] = rs2.getString(i+1);
            }
            vector2.addElement(arrList);
        }

        if (vector1.size() != vector2.size()) return false;
        if (vector1.size()==0 || vector2.size()==0) return false;

        for (int i=0; i<vector1.size() ; i++) {
            String[] arr1 = (String[])vector1.elementAt(i);
            String[] arr2 = (String[])vector2.elementAt(i);
            for (int j=0 ; j<arr1.length ; j++) {
                if (!arr1[j].equals(arr2[j])) return false;
            }
        }

        return true;
    }   
}
